package com.fc.redis.util;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.springframework.transaction.interceptor.TransactionAspectSupport;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * @program: fc-redis->ExcelUtil
 * @description: excelUtil
 * @author: G_Y
 * @create: 2019-09-06 23:56
 **/
public class ExcelUtil {

    /**
     * excel数据导入到对象集合
     *
     * @param xlsFile
     * @param cls
     * @param fieldNames 每一格数据写入对象的属性对应的名称(按顺序依次)
     * @param beginRow   开始读取excel的数据行 1开始代表第一行
     * @param <T>
     * @return
     */
    public static <T> List<T> excel2BeanList(MultipartFile xlsFile, Class<T> cls, List<String> fieldNames, int beginRow) {
        Workbook wb = null;
        InputStream im = null;
        List<T> list = null;
        try {
            im = xlsFile.getInputStream();
            wb = WorkbookFactory.create(im);
            // 根据页面index 获取sheet页
            Sheet sheet = wb.getSheetAt(0);
            list = new ArrayList<>(sheet.getPhysicalNumberOfRows());
            Row row = null;
            // 循环sheet页中数据从第x行开始,例:第3行开始为导入数据
            for (int i = beginRow - 1; i < sheet.getPhysicalNumberOfRows(); i++) {
                // 获取每一行数据
                row = sheet.getRow(i);
                T t = row2Bean(row, cls, fieldNames);
                list.add(t);
            }
        } catch (Exception e1) {
            // 回滚数据
            TransactionAspectSupport.currentTransactionStatus().setRollbackOnly();
            e1.printStackTrace();
            throw new RuntimeException("excel数据读取异常");
        } finally {
            try {
                im.close();
                wb.close();
            } catch (IOException e2) {
                e2.printStackTrace();
            }
        }
        return list;
    }

    private static <T> T row2Bean(Row row, Class<T> cls, List<String> fieldNames) {
        T t = null;
        try {
            t = cls.newInstance();
            int i = 0;
            for (String fieldName : fieldNames) {
                Field declaredField = cls.getDeclaredField(fieldName);
                declaredField.setAccessible(true);
                Cell cell = row.getCell(i);
                sell2BeanField(t, declaredField, cell);
                i++;
            }
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("excel数据读入异常");
        }
        return t;
    }

    private static void sell2BeanField(Object obj, Field declaredField, Cell cell) throws IllegalAccessException {
        if (null == cell || "" == cell.toString()) {
            declaredField.set(obj, null);
            return;
        }
        Class<?> type = declaredField.getType();
        ClassTypeHandler classTypeHandler = map.get(type);
        if (classTypeHandler == null) {
            throw new RuntimeException("excel读取到对象无法匹配对象属性类型");
        }
        classTypeHandler.handle(cell, obj, declaredField);
    }

    private static Map<Class<?>, ClassTypeHandler> map = new HashMap<>();

    static {
        map.put(Integer.class, ClassTypeHandler.INT);
        map.put(int.class, ClassTypeHandler.INT);
        map.put(String.class, ClassTypeHandler.STRING);
        map.put(double.class, ClassTypeHandler.DOUBLE);
        map.put(Double.class, ClassTypeHandler.DOUBLE);
        map.put(boolean.class, ClassTypeHandler.BOOLEAN);
        map.put(Boolean.class, ClassTypeHandler.BOOLEAN);
        map.put(char.class, ClassTypeHandler.CHAR);
        map.put(Character.class, ClassTypeHandler.CHAR);
        map.put(Date.class, ClassTypeHandler.DATE);
        map.put(BigDecimal.class, ClassTypeHandler.BIGDECIMAL);
        map.put(Float.class, ClassTypeHandler.FLOAT);
        map.put(float.class, ClassTypeHandler.FLOAT);
    }

    private enum ClassTypeHandler {
        INT {
            @Override
            public void handle(Cell cell, Object obj, Field declaredField) throws IllegalAccessException {
                cell.setCellType(CellType.STRING);
                String str = cell.getStringCellValue();
                declaredField.set(obj, Integer.valueOf(str));
            }
        },
        STRING {
            @Override
            public void handle(Cell cell, Object obj, Field declaredField) throws IllegalAccessException {
                // 如果表格内容为数字,需要设置CellType为string，否则调用getStringCellValue()会出现获取类型错误
                cell.setCellType(CellType.STRING);
                declaredField.set(obj, cell.getStringCellValue());
            }
        },
        BOOLEAN {
            @Override
            public void handle(Cell cell, Object obj, Field declaredField) throws IllegalAccessException {
                boolean booleanCellValue = cell.getBooleanCellValue();
                declaredField.set(obj, booleanCellValue);
            }
        },
        DOUBLE {
            @Override
            public void handle(Cell cell, Object obj, Field declaredField) throws IllegalAccessException {
                double numericCellValue = cell.getNumericCellValue();
                declaredField.set(obj, numericCellValue);
            }
        },
        FLOAT {
            @Override
            public void handle(Cell cell, Object obj, Field declaredField) throws IllegalAccessException {
                cell.setCellType(CellType.STRING);
                String str = cell.getStringCellValue();
                declaredField.set(obj, Float.valueOf(str));
            }
        },
        DATE {
            @Override
            public void handle(Cell cell, Object obj, Field declaredField) throws IllegalAccessException {
                try {
                    declaredField.set(obj, new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(cell.getDateCellValue()));
                } catch (Exception e) {
                    e.printStackTrace();
                    try {
                        declaredField.set(obj, new SimpleDateFormat("yyyy-MM-dd").format(cell.getDateCellValue()));
                    } catch (Exception e1) {
                        e1.printStackTrace();
                        throw new RuntimeException("excel读入Date日期格式数据转换异常");
                    }
                }
            }
        },
        BIGDECIMAL {
            @Override
            public void handle(Cell cell, Object obj, Field declaredField) throws IllegalAccessException {
                double numericCellValue = cell.getNumericCellValue();
                BigDecimal bg = new BigDecimal(numericCellValue);
                declaredField.set(obj, bg);
            }
        },
        CHAR {
            @Override
            public void handle(Cell cell, Object obj, Field declaredField) throws IllegalAccessException {
                cell.setCellType(CellType.STRING);
                String str = cell.getStringCellValue();
                char c = ' ';
                if (StringUtils.isNotBlank(str)) {
                    c = str.toCharArray()[0];
                }
                declaredField.set(obj, c);
            }
        };

        public abstract void handle(Cell cell, Object obj, Field declaredField) throws IllegalAccessException;
    }

    public static <T> void exportExcel(HttpServletResponse response, ExportExcelBean<T> excelBean) {
        HSSFWorkbook workbook = new HSSFWorkbook();
        //此处添加数据
        OutputStream os = null;
        try {
            HSSFSheet sheet = workbook.createSheet(excelBean.getSheetName());
            // 写excel表头
            HSSFRow headerRow1 = sheet.createRow(0);
            for (int i = 0; i < excelBean.getColumnCount(); i++) {
                headerRow1.createCell(i).setCellValue(excelBean.getTableHeader()[i]);
            }
            // 写数据行
            writeExportExcelRow(sheet, excelBean);
            //清空response
            response.reset();
            response.setContentType("multipart/form-data");
            response.setHeader("Content-Disposition",
                    "attachment; filename=" + new String(excelBean.getFileName().getBytes(), "iso8859-1") + ".xls");
            os = new BufferedOutputStream(response.getOutputStream());
            workbook.write(os);
            os.flush();
        } catch (Exception e) {
            e.printStackTrace();
            throw new RuntimeException("导出数据异常");
        } finally {
            try {
                if (os != null) {
                    os.close();
                }
                workbook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
    }

    private static <T> void writeExportExcelRow(HSSFSheet sheet, ExportExcelBean<T> excelBean) {
        List<T> rows = excelBean.getRows();
        int i = 1;
        for (T obj : rows) {
            HSSFRow row = sheet.createRow(i);
            excelBean.writeExportExcelRow(row, obj);
            i++;
        }

    }

}
